CREATE TYPE inforoute AS(
id_route Integer,
num_user integer
);

CREATE TYPE route AS(
id_route integer,
source character varying,
destination character varying
);


-- DROP SEQUENCE sequenceidcity;

CREATE SEQUENCE sequenceidcity
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 9
  CACHE 1;
ALTER TABLE sequenceidcity OWNER TO postgres;

-- Function: addcity(character varying, character varying)

-- DROP FUNCTION addcity(character varying, character varying);

CREATE OR REPLACE FUNCTION addcity(character varying, character varying)
  RETURNS integer AS
$BODY$
DECLARE 
	id INTEGER := NextVal('SequenceIdCity');
BEGIN
    IF((SELECT COUNT(*) FROM tbstate WHERE state_name = $2)>0) THEN
		IF((SELECT COUNT(*) FROM tbcity WHERE city_name=$1 AND city_state=$2 ) < 1) THEN
			INSERT INTO tbcity(city_name, city_state, city_id) VALUES($1, $2, id);
			RETURN 1; --se ADICIONO con exito.
		ELSE
			RETURN -1;--sI YA EXISTE ESTA Ciudad en la tabla
		END IF;
	ELSE 
		RETURN -2;-- NO EXITE EL DEPARTAMENTO
	END IF;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION addcity(character varying, character varying) OWNER TO postgres;

-- Function: adddriver(integer, character varying, character varying)

-- DROP FUNCTION adddriver(integer, character varying, character varying);

CREATE OR REPLACE FUNCTION adddriver(integer, character varying, character varying)
  RETURNS integer AS
$BODY$
BEGIN
	IF((SELECT COUNT(*) FROM tbdriver WHERE driver_id = $1) < 1)THEN
		INSERT INTO tbdriver(driver_id, driver_name, driver_type) VALUES($1, $2, $3);
		return 1;--adiciono correctamente
	ELSE
		return -2; --no se adiciono por que el id ya existe
	END IF;	
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION adddriver(integer, character varying, character varying) OWNER TO postgres;

-- Function: consultroute()

-- DROP FUNCTION consultroute();

CREATE OR REPLACE FUNCTION consultroute()
  RETURNS SETOF route AS
$BODY$
BEGIN
RETURN QUERY
SELECT r. r_id, ct1.city_name, ct2.city_name from tbcity ct1,
tbcity ct2, tbroute r where ct1.city_id=r.r_source AND ct2.city_id=r.r_destination; 
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION consultroute() OWNER TO postgres;


-- Function: deletecity(integer)

-- DROP FUNCTION deletecity(integer);

CREATE OR REPLACE FUNCTION deletecity(integer)
  RETURNS void AS
$BODY$
BEGIN
	DELETE FROM tbcity 
	WHERE city_id=$1;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION deletecity(integer) OWNER TO postgres;

-- Function: deletedriver(integer)

-- DROP FUNCTION deletedriver(integer);

CREATE OR REPLACE FUNCTION deletedriver(integer)
  RETURNS void AS
$BODY$
BEGIN
	DELETE FROM tbdriver 
	WHERE driver_id=$1;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION deletedriver(integer) OWNER TO postgres;

-- Function: inforoutes()

-- DROP FUNCTION inforoutes();

CREATE OR REPLACE FUNCTION inforoutes()
  RETURNS SETOF inforoute AS
$BODY$
BEGIN 
RETURN QUERY
SELECT t_idroute, COUNT(*)::integer from tbtickets group by t_idroute;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION inforoutes() OWNER TO postgres;

-- Function: seekallcities()

-- DROP FUNCTION seekallcities();

CREATE OR REPLACE FUNCTION seekallcities()
  RETURNS SETOF tbcity AS
$BODY$
BEGIN
  RETURN QUERY
    SELECT *
    FROM tbcity;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION seekallcities() OWNER TO postgres;

-- Function: seekalldrivers()

-- DROP FUNCTION seekalldrivers();

CREATE OR REPLACE FUNCTION seekalldrivers()
  RETURNS SETOF tbdriver AS
$BODY$
BEGIN
  RETURN QUERY
    SELECT *
    FROM tbdriver;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION seekalldrivers() OWNER TO postgres;

-- Function: seekcities()

-- DROP FUNCTION seekcities();

CREATE OR REPLACE FUNCTION seekcities()
  RETURNS SETOF tbcity AS
$BODY$
BEGIN
  RETURN QUERY
    SELECT *
    FROM tbcity;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION seekcities() OWNER TO postgres;

-- Function: updatecity(integer, character varying, character varying)

-- DROP FUNCTION updatecity(integer, character varying, character varying);

CREATE OR REPLACE FUNCTION updatecity(integer, character varying, character varying)
  RETURNS integer AS
$BODY$
DECLARE

BEGIN
	IF((SELECT COUNT(*) FROM tbstate WHERE state_name = $3)>0) THEN
		IF((SELECT COUNT(*) FROM tbcity WHERE city_name=$2 AND city_state=$3 ) < 1) THEN
			UPDATE tbcity SET city_name=$2, city_state=$3
			WHERE city_id=$1;
			RETURN 1; --se modifico con exito.
		ELSE
			RETURN -1;--sI YA EXISTE ESTA Ciudad en la tabla
		END IF;
	ELSE 
		RETURN -2;-- NO EXITE EL DEPARTAMENTO
	END IF;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION updatecity(integer, character varying, character varying) OWNER TO postgres;


-- Function: updatedriver(integer, character varying, character varying)

-- DROP FUNCTION updatedriver(integer, character varying, character varying);

CREATE OR REPLACE FUNCTION updatedriver(integer, character varying, character varying)
  RETURNS integer AS
$BODY$
BEGIN
	
		UPDATE tbdriver SET  driver_name=$2, driver_type=$3
		WHERE driver_id=$1;
		return 1;--se modifico correctamente
	
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION updatedriver(integer, character varying, character varying) OWNER TO postgres;

